package hu.crs.demobank.backend;

import hu.crs.demobank.backend.dto.TransferDTO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DbOperations {
	private static Logger logger = Logger.getLogger(DbOperations.class);

	@SuppressWarnings("unused")
	public static Connection getConnection() {
		Context ctx;
		Connection conn = null;
		try {
			ctx = new InitialContext();

			if (ctx == null) {
				logger.debug("ERROR: No context!");
				throw new Exception();
			}

			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/demobankdb");

			if (ds == null) {
				logger.debug("ERROR: No Datasource have found!");
				throw new Exception();
			}

			conn = ds.getConnection();

			if (conn == null) {
				logger.debug("ERROR: No connection");
				throw new Exception();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public static boolean isCorrectLogin(String userName, String password) {
		Connection conn = DbOperations.getConnection();
		int count = 0;
		try {
			PreparedStatement loginIdCounterPS = null;
			String loginIdCounterSelect = "select COUNT(*) as cnt from T_LOGIN where username=? and password=?";
			loginIdCounterPS = conn.prepareStatement(loginIdCounterSelect);
			loginIdCounterPS.setString(1, userName);
			loginIdCounterPS.setString(2, password);
			ResultSet rs = loginIdCounterPS.executeQuery();
			rs.next();
			count = rs.getInt("cnt");

			logger.debug("Username counter " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return count > 0;
	}

	public static Integer getLoginID(String username) throws Exception {
		Integer loginid = null;
		try {
			Connection conn = getConnection();
			PreparedStatement ps = null;
			String psSelect = "select * from T_LOGIN where USERNAME=?";
			ps = conn.prepareStatement(psSelect);
			ps.setString(1, username);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				loginid = rs.getInt("LOGINID");
			} else {
				logger.debug("ERROR: No login record have found.");
				throw new Exception();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return loginid;

	}

	public static String getAccountNumber(int userId) throws Exception {
		String accountNumber = null;
		try {
			Connection conn = getConnection();
			PreparedStatement ps = null;
			String psSelect = "select * from T_ACCOUNT where LOGINID=?";
			ps = conn.prepareStatement(psSelect);
			ps.setInt(1, userId);
			ResultSet rs = ps.executeQuery();

			if (rs.next()) {
				accountNumber = rs.getString("ACCOUNT_NUMBER");
			} else {
				logger.debug("ERROR: No account have found.");
				throw new Exception();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return accountNumber;
	}

	public static Integer persistTransfer(TransferDTO transferDTO) {
		PreparedStatement statement;
		try {
			Connection conn = getConnection();
			String sqlInsert = "insert into T_TRANSFER (FROM_ACCOUNT,TO_ACCOUNT,AMOUNT,COMMENT,CREATE_TS) values(?,?,?,?,?)";
			statement = conn.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS );
			statement.setString(1, transferDTO.getFromAccount());
			statement.setString(2, transferDTO.getToAccount());
			statement.setInt(3, transferDTO.getAmount());
			statement.setString(4, transferDTO.getComment());
			statement.setDate(5, new java.sql.Date(transferDTO.getCreateTS().getTime()));
			logger.debug(statement);

			statement.executeUpdate();
			
			ResultSet rs = statement.getGeneratedKeys();
			rs.next();
			Integer id = rs.getInt(1);
			
			return id;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
}
